﻿#include "MyDatabase.h"
#include <sqlpp11/sqlpp11.h>
#include <sqlpp11/odbc/odbc.h>
#include <sqlpp11/sqlite3/sqlite3.h>
#include "orm.h"

namespace sql = sqlpp::sqlite3;

std::shared_ptr<sql::connection> openDB(String fileName, int flags)
{
	sql::connection_config config;
	config.path_to_database = fileName;
	config.flags = flags;
	config.debug = false;

	try {
		return std::make_shared<sql::connection>(config);
	}
	catch (...) {
	}

	return nullptr;
}

std::shared_ptr<sql::connection> createDB(String fileName)
{
	if (FileUtil::existFile(U2A(fileName)) && !FileUtil::deleteFile(U2A(fileName)))
	{
		return nullptr;
	}

	auto saveDB = openDB(fileName, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE);

	if (saveDB) 
	{
		String sql2 = "CREATE TABLE IF NOT EXISTS ObserveData(\
				gid INTEGER PRIMARY KEY,\
				pid TEXT,\
				x REAL,\
				y REAL,\
				z REAL,\
				time TIMESTAMP DEFAULT(DATETIME('now', 'localtime')),\
				project TEXT,\
				projectType TEXT,\
				desc TEXT\
				)";
		String sql3 = "CREATE TABLE IF NOT EXISTS Project(\
				gid INTEGER PRIMARY KEY,\
				name TEXT,\
				desc TEXT\
				)";
		String sql4 = "CREATE TABLE IF NOT EXISTS GeoObserve(\
				gid INTEGER PRIMARY KEY,\
				time TIMESTAMP DEFAULT(DATETIME('now', 'localtime')),\
				desc TEXT\
				)";

		saveDB->execute(sql2);
		saveDB->execute(sql3);
		saveDB->execute(sql4);
	}

	return saveDB;
}

String MyDatabase::listGeoObserveTime()
{
	Json::Value value;

	if (connection)
	{
		CORE_LOCK_AUTO_MUTEX;
		sql::connection& db = *connection;
		MyTab::table_GeoObserve table = {};

		auto result = (db)(custom_query(sqlpp::select(), select_flags(sqlpp::distinct), select_columns(table.time), from(table),
			sqlpp::unconditionally()));

		for (const auto& row : result)
		{
			value.append(row.time.value());
		}
	}

	return ServerUtil::jsonToString(value);
}

String MyDatabase::getGeoObserveData(String time)
{
	if (connection)
	{
		CORE_LOCK_AUTO_MUTEX;
		Json::Value value;

		sql::connection& db = *connection;
		MyTab::table_GeoObserve table = {};
		auto result = (db)(select(all_of(table)).from(table).where(
			table.time == time
			));
		for (const auto& row : result)
		{
			Json::Value value1;
			value1["gid"] = row.gid.value();
			value1["time"] = row.time.value();
			String desc = row.desc.value();
			Json::Value value2;
			Json::Reader reader;
			reader.parse(desc, value2);
			value1["desc"] = value2;
			value.append(value1);
		}
		return ServerUtil::jsonToString(value);
	}

	return "false";
}

bool MyDatabase::insertGeoObserve(String time, String desc)
{
	if (connection)
	{
		CORE_LOCK_AUTO_MUTEX;
		sql::connection& db = *connection;
		MyTab::table_GeoObserve table_Project = {};
		auto res = (db)(insert_into(table_Project).set(
			table_Project.time = time
			, table_Project.desc = desc));
		if (res > 0)
		{
			return true;
		}
	}

	return false;
}
String MyDatabase::create(String fileName)
{
	CORE_LOCK_AUTO_MUTEX;
	connection = createDB(fileName);
	return connection ? "true" : "false";
}

void MyDatabase::close()
{
	CORE_LOCK_AUTO_MUTEX;
	connection = nullptr;
}

String MyDatabase::open(String fileName)
{
	CORE_LOCK_AUTO_MUTEX;
	connection = openDB(fileName, SQLITE_OPEN_READWRITE);
	return connection ? "true" : "false";
}

bool MyDatabase::insert(String pid, String time, double x, double y, double z, String project, String projectType, String desc)
{
	if (connection)
	{
		CORE_LOCK_AUTO_MUTEX;
		sql::connection& db = *connection;
		MyTab::table_ObserveData table_Project = {};
		auto res = (db)(insert_into(table_Project).set(
			table_Project.pid = pid
			, table_Project.x = x
			, table_Project.y = y
			, table_Project.z = z
			, table_Project.time = time
			, table_Project.project = project
			, table_Project.projectType = projectType
			, table_Project.desc = desc));
		if (res > 0)
		{
			return true;
		}
	}

	return false;
}

bool MyDatabase::update(String pid, String time, double x, double y, double z, String project, String projectType, String desc)
{
	if (connection)
	{
		CORE_LOCK_AUTO_MUTEX;
		sql::connection& db = *connection;
		MyTab::table_ObserveData table_Project = {};
		auto res = (db)(sqlpp::update(table_Project).set(
			table_Project.x = x
			, table_Project.y = y
			, table_Project.z = z
			, table_Project.desc = desc).where(
				table_Project.pid == pid and
				table_Project.time == time and
				table_Project.projectType == projectType and
				table_Project.project == project
				));
		if (res > 0)
		{
			return true;
		}
	}

	return false;
}

bool MyDatabase::existProject(String name)
{
	if (connection)
	{
		CORE_LOCK_AUTO_MUTEX;
		sql::connection& db = *connection;
		MyTab::table_Project table_Project = {};
		auto result = (db)(select(count(table_Project.gid)).from(table_Project).where(table_Project.name == name));
		auto count = result.front().count;
		return count > 0;
	}

	return false;
}
bool MyDatabase::deleteADay(String time, String project, String projectType)
{
	if (connection)
	{
		CORE_LOCK_AUTO_MUTEX;
		sql::connection& db = *connection;
		MyTab::table_ObserveData table_Project = {};
		auto res = (db)(sqlpp::remove_from(table_Project).where(
			table_Project.time == time and
			table_Project.project == project and
			table_Project.projectType == projectType
			));
		if (res > 0)
		{
			return true;
		}
	}

	return false;
}

String MyDatabase::getProjectData(String project,String projectType)
{
	if (connection)
	{
		CORE_LOCK_AUTO_MUTEX;
		Json::Value value;

		sql::connection& db = *connection;
		MyTab::table_ObserveData table = {};
		auto result = (db)(select(all_of(table)).from(table).where(
			table.project == project and table.projectType == projectType
			));
		for (const auto& row : result)
		{
			Json::Value value1;
			value1["x"] = row.x.value();
			value1["y"] = row.y.value();
			value1["z"] = row.z.value();
			value1["id"] = row.pid.value();
			value1["time"] = row.time.value();

			String desc = row.desc.value();
			Json::Value value2;
			Json::Reader reader;
			reader.parse(desc,value2);
			value1["desc"] = value2;
			value.append(value1);
		}
		return ServerUtil::jsonToString(value);
	}

	return "false";
}

String MyDatabase::insertProject(String project,String desc)
{
	if (existProject(project))
	{
		return "true";
	}

	if (connection)
	{
		CORE_LOCK_AUTO_MUTEX;
		sql::connection& db = *connection;

		MyTab::table_Project table = {};
		auto res = (db)(insert_into(table).set(
			table.name = project));
		if (res > 0)
		{
			return "true";
		}
	}

	return "false";
}


String MyDatabase::updateProject(String project, String desc)
{
	if (connection)
	{
		CORE_LOCK_AUTO_MUTEX;
		sql::connection& db = *connection;
		MyTab::table_Project table = {};
		auto res = (db)(sqlpp::update(table).set(table.desc = desc).where(table.name == project));
		if (res > 0)
		{
			return "true";
		}
	}

	return "false";
}

String MyDatabase::listTime()
{
	Json::Value value;

	if (connection)
	{
		CORE_LOCK_AUTO_MUTEX;
		sql::connection& db = *connection;
		MyTab::table_ObserveData table = {};

		auto result = (db)(custom_query(sqlpp::select(), select_flags(sqlpp::distinct), select_columns(table.time), from(table),
			sqlpp::unconditionally()));

		for (const auto& row : result)
		{
			value.append(row.time.value());
		}
	}

	return ServerUtil::jsonToString(value);
}

String MyDatabase::listProjectInTime(String time)
{
	Json::Value value;

	if (connection)
	{
		CORE_LOCK_AUTO_MUTEX;
		sql::connection& db = *connection;
		MyTab::table_ObserveData table = {};

		auto result = (db)(custom_query(sqlpp::select(), select_flags(sqlpp::distinct), select_columns(table.project), from(table),
			sqlpp::where(table.time == time or table.time == time + " 12:00:00")));

		for (const auto& row : result)
		{
			value.append(row.project.value());
		}
	}

	return ServerUtil::jsonToString(value);
}

String MyDatabase::listProject()
{
	Json::Value value;

	if (connection)
	{
		CORE_LOCK_AUTO_MUTEX;
		sql::connection& db = *connection;
		MyTab::table_Project table = {};
		auto result = (db)(select(all_of(table)).from(table).unconditionally());
		for (const auto& row : result)
		{
			String name = row.name;
			String desc = row.desc;
			Json::Value value1;
			Json::Reader reader;
			reader.parse(desc, value1);
			value1["name"] = name;
			value.append(value1);
		}
	}

	return ServerUtil::jsonToString(value);
}
